/*
 * #%L
 * SwiftUIDataAccessLayer
 * $Id:$
 * $HeadURL:$
 * %%
 * Copyright (C) 2011 - 2012 Linkwithweb
 * %%
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as
 * published by the Free Software Foundation, either version 3 of the 
 * License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public 
 * License along with this program.  If not, see
 * <http://www.gnu.org/licenses/gpl-3.0.html>.
 * #L%
 */
/**
 * 
 */
package com.linkwithweb.products.swiftui.dao.impl;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;


/**
 * @author ashwin kumar
 * 
 */
public class DBHelper extends AbstractDAOImpl {

	private static DBHelper dbHelper = new DBHelper();

	/**
	 * @return
	 */
	public static DBHelper getInstance() {
		return dbHelper;
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
	}

	public Map<String, Integer> getMetaData(String tableName) {
		Map<String, Integer> metaData = new LinkedHashMap<String, Integer>();
		try {
			Connection conn = getJdbcTemplate("jdbcTemplate").getDataSource().getConnection();

			// Get a statement from the connection
			Statement stmt = conn.createStatement();

			// Execute the query
			ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName
					+ " WHERE 1=2");

			// Get the metadata
			ResultSetMetaData md = rs.getMetaData();

			// Print the column labels
			for (int i = 1; i <= md.getColumnCount(); i++) {
				metaData.put(md.getColumnName(i), md.getColumnType(i));
			}
			stmt.close();
			conn.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return metaData;
	}

	/**
	 * @param sql
	 * @return
	 */
	public Map<String, String> getMetaDataForSQL(String sql,
			boolean isDashboardDB) {
		Map<String, String> metaData = new LinkedHashMap<String, String>();
		try {
			Connection conn = getJdbcTemplate("jdbcTemplate")
					.getDataSource().getConnection();

			// Get a statement from the connection
			Statement stmt = conn.createStatement();

			int whereIndex = StringUtils
					.lastIndexOf(sql.toUpperCase(), "WHERE");
			int endBracketIndex = StringUtils.lastIndexOf(sql.toUpperCase(),
					")");

			if (whereIndex < endBracketIndex) {
				sql = sql + " WHERE 1=2";
			} else {
				if (StringUtils.containsIgnoreCase(sql, "where")) {
					sql = sql + " AND 1=2";
				} else {
					sql = sql + " WHERE 1=2";
				}
			}

			// Execute the query
			ResultSet rs = stmt.executeQuery(sql);

			// Get the metadata
			ResultSetMetaData md = rs.getMetaData();

			// Print the column labels
			for (int i = 1; i <= md.getColumnCount(); i++) {
				metaData.put(md.getColumnName(i), md.getColumnTypeName(i));
			}
			stmt.close();
			conn.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return metaData;
	}

	/**
	 * @param sql
	 * @return
	 */
	public List<String> getPrimaryKeys(String tableName, boolean isDashboardDB) {
		Map<String, Integer> metaData = new LinkedHashMap<String, Integer>();
		try {
			Connection conn = getJdbcTemplate("jdbcTemplate")
					.getDataSource().getConnection();

			DatabaseMetaData meta = conn.getMetaData();
			// The Oracle database stores its table names as Upper-Case,
			// if you pass a table name in lowercase characters, it will not
			// work.
			// MySQL database does not care if table name is
			// uppercase/lowercase.
			//
			ResultSet rs = meta.getPrimaryKeys(null, null, tableName.toUpperCase());

			java.util.List<String> list = new java.util.ArrayList<String>();
			while (rs.next()) {
				String columnName = rs.getString("COLUMN_NAME");
				list.add(columnName);
			}
			conn.close();
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return new ArrayList();
	}

}
